import json
from copy import copy
from datetime import timedelta

import requests
from openpyxl.utils import get_column_letter
from urllib3 import encode_multipart_formdata

from db_utils import MysqlEngine
from utils import *


class DataPush:
    def __init__(self, conn: MysqlEngine):
        self.conn =conn
        self.Master_folder = 'E:/DataPush/'
    def push_text(self, robot_url, msg, inform_all=False):
        if inform_all:
            body = json.dumps(
                {
                    "msgtype": "text",
                    "text": {
                        "content": msg,
                        "mentioned_list": ["@all"],
                    }

                })
            r = requests.post(url=robot_url, data=body)
        else:
            body = json.dumps(
                {
                    "msgtype": "text",
                    "text": {
                        "content": msg
                    }

                })
            r = requests.post(url=robot_url, data=body)
    def push_markdown(self, robot_url, msg, inform_all=False):
        print(inform_all)
        body = json.dumps(
            {
                "msgtype": "markdown",
                "markdown": {
                    "content": msg
                }

            })
        r = requests.post(url=robot_url, data=body)
    def push_file(self, file_path, robot_url, msg=None, inform_all=False, msgtype='text', file_type='file'):
        func = {'text': self.push_text, 'markdown': self.push_markdown}
        robot_key = robot_url.split('key=')[1]
        upload_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key={robot_key}&type={file_type}'
        file_name = file_path.split("/")[-1]
        with open(file_path, 'rb') as f:
            length = os.path.getsize(file_path)
            data = f.read()
        headers = {"Content-Type": "application/octet-stream"}
        params = {
            "filename": file_name,
            "filelength": length,
        }
        file_data = copy(params)
        file_data['file'] = (file_path.split('/')[-1:][0], data)
        encode_data = encode_multipart_formdata(file_data)
        file_data = encode_data[0]
        headers['Content-Type'] = encode_data[1]
        r = requests.post(upload_url, data=file_data, headers=headers)
        media_id = r.json()['media_id']
        headers = {"Content-Type": "text/plain"}
        data = {
            "msgtype": "file",
            "file": {
                "media_id": media_id
            }
        }
        if msgtype in func.keys():
            func[msgtype](robot_url, msg, inform_all)
        else:
            print("未知类型")
        r = requests.post(
            url=robot_url,
            headers=headers, json=data)
    def push_tiktok_new_anchor_add(self, robot_url, inform_all=False):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        month = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-01')
        daily_anchor_number = self.conn.fetch_dat('''
                            SELECT
                                count(anchor_name) num
                            FROM
                                tiktok.ods_anchor_list_lst
                            WHERE
                                join_time = CURRENT_DATE
                        ''')
        month_anchor_number = self.conn.fetch_dat(f'''
            SELECT
                count(anchor_name) num
            FROM
                tiktok.ods_anchor_list_lst
            WHERE
                update_date = CURRENT_DATE and join_time >= '{month}'
        ''')
        msg = f"# 抖音新增主播\n> 今日新增:<font color=\"info\">**{str(daily_anchor_number.num[0])}**</font>\n> 本月新增:<font color=\"info\">**{str(month_anchor_number.num[0])}**</font>\n\n###### <font color=\"comment\">新增主播信息如下:</font>"
        dat_daily = self.conn.fetch_dat('''
                        SELECT
                            anchor_name
                            ,anchor_id
                            ,fans
                            ,share_rate
                            ,operator
                        FROM
                            tiktok.ods_anchor_list_lst
                        WHERE
                            join_time = CURRENT_DATE
                    ''')
        file_path = self.Master_folder + f'push_new_anchor/抖音新增主播_{now_time}.xlsx'
        dat_daily.rename(columns={'anchor_name': '主播名',
                            'anchor_id': '主播ID',
                            'fans': '粉丝数',
                            'share_rate': '分成比',
                            'operator': '运营经纪人'
                            }, inplace=True)
        dat_daily.to_excel(file_path, sheet_name='今日新增', index=False)
        month_data = self.conn.fetch_dat(f'''
            SELECT
                anchor_name
                ,anchor_id
                ,fans
                ,share_rate
                ,operator
                ,join_time
            FROM
                tiktok.ods_anchor_list_lst
            WHERE
                update_date = CURRENT_DATE and join_time >= '{month}'
            ORDER BY
                join_time DESC
        ''')
        month_data.rename(columns={'anchor_name': '主播名',
                                   'anchor_id': '主播ID',
                                   'fans': '粉丝数',
                                   'share_rate': '分成比',
                                   'operator': '运营经纪人',
                                   'join_time': '入会时间',
                                   }, inplace=True)
        append_to_excel(month_data, file_path, sheet_name='本月新增')
        auto_columns_size(file_path)
        self.push_file(file_path, robot_url, msg, inform_all, msgtype='markdown')
    def push_tiktok_new_anchor_task(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        dat = self.conn.fetch_dat('''
        SELECT
            anchor_name
            ,illegal_status
            ,task_type
            -- ,task_start_time
            ,DATE_ADD(task_start_time,INTERVAL 30 day)  task_end_time
            ,case when task_type = 1 then 30 - task_day
                        when task_type = 2 then 60 - task_day
                        when task_type = 3 then 90 - task_day END residue_days
            ,case when bb.live_hour - aa.live_hour >0 then bb.live_hour - aa.live_hour else 0 end diff_hour
            ,case when bb.live_days - aa.live_day >0 then bb.live_days - aa.live_day else 0 end diff_days
            ,tiktok_coin
            ,rate
            ,tiktok_coin*0.1*rate estimated_amount
            ,next_rate
            ,case when tiktok_coin < 100000 then  tiktok_coin_max - tiktok_coin+1 else 0 end coin_dif
            ,(case when tiktok_coin < 100000 then  tiktok_coin_max+1 else tiktok_coin end)*0.1*next_rate  next_estimated_amount
            ,(case when tiktok_coin < 100000 then  tiktok_coin_max+1 else tiktok_coin end)*0.1*next_rate - tiktok_coin*0.1*rate  next_estimated_amount_dif
        FROM
            (SELECT
                *
            FROM
                tiktok.ods_new_anchor_task_lst
            WHERE
                update_date = CURRENT_DATE and tiktok_coin>0
            )aa
            LEFT JOIN
            (
            SELECT
                *
            FROM
                task.task_tiktok_new_anchor
            )bb
        ON aa.task_type = bb.step AND (aa.tiktok_coin BETWEEN tiktok_coin_min and tiktok_coin_max)
        ORDER BY tiktok_coin DESC
        ''')
        dat.rename(columns={
            'anchor_name': '主播名',
            # 'task_start_time': '拉新任务开始日期',
            'illegal_status': '违规信息',
            'task_type': '拉新任务周期',
            'task_end_time': '本期任务结束日期',
            # (结束日期当天数据不算本期)
            'residue_days': '本期任务剩余天数',
            # (含推送当天)
            'diff_hour': '有效直播小时差',
            'diff_days': '有效直播天数差',
            'tiktok_coin': '当前音浪',
            'rate': '奖励比例',
            'estimated_amount': '完成任务预估奖励',
            'next_rate': '下阶段奖励比例',
            'coin_dif': '下阶段音浪差',
            'next_estimated_amount': '下阶段预估奖励',
            'next_estimated_amount_dif': '预计增加'
        }, inplace=True)
        file_path = self.Master_folder + f'push_new_anchor_task/拉新任务完成进度_{now_time}.xlsx'
        dat.to_excel(file_path, index=False)
        auto_columns_size(file_path)
        text = '抖音拉新任务监控如下:'
        self.push_file(file_path, robot_url, text)
    def push_tiktok_basic_task(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        dat = self.conn.fetch_dat('''
        SELECT
            anchor_name
            ,illegal_status
            ,if(upload_days-video_days>0,upload_days-video_days,0) diff_upload_days
            ,if(upload_numbers-video_number>0,upload_numbers-video_number,0) diff_video_number
            ,if(task_hour-live_hour>0,task_hour-live_hour,0) diff_live_hour
            ,if(task_days-live_days>0,task_days-live_days,0) diff_live_days
            ,tiktok_coin
            ,tiktok_coin*0.1*rate estimated_amount
        FROM
            tiktok.ods_process_basic_lst
        CROSS JOIN
            task.task_basic
        WHERE
            update_date = CURRENT_DATE 
        ORDER BY
            tiktok_coin DESC
                ''')
        dat.rename(columns={
            "anchor_name":"主播名",
            "illegal_status":"违规信息",
            "diff_upload_days":"短视频投稿天数差",
            "diff_video_number":"短视频合格数差",
            "diff_live_hour":"有效直播时长差",
            "diff_live_days":"有效直播天数差",
            "tiktok_coin":"当前音浪",
            "estimated_amount":"完成任务预估收入"
        }, inplace=True)
        file_path = self.Master_folder + f'push_basic_task/基线任务完成进度_{now_time}.xlsx'
        dat.to_excel(file_path, index=False)
        auto_columns_size(file_path)
        text = '抖音基线任务监控如下:'
        self.push_file(file_path, robot_url, text)
    def push_tiktok_process_active_task(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        dat = self.conn.fetch_dat('''
            SELECT
                anchor_name
                ,illegal_status
                ,tiktok_coin
                ,if(task_live_hour-live_hour>0,task_live_hour-live_hour,0) diff_hour
                ,if(task_live_days-live_days>0,task_live_days-live_days,0) diff_days
                ,estimated_rate
                ,estimated_amount
                ,rate next_rate
                ,tiktok_coin*0.1*rate next_estimated_amount
            FROM
                (SELECT
                    *
                FROM
                    tiktok.ods_process_active_lst
                WHERE update_date = CURRENT_DATE
                    ) aa
            LEFT JOIN
                (SELECT
                    *
                FROM
                    task.task_tiktok_process_active) bb
            on if(aa.now_step<7,aa.now_step+1,aa.now_step) = bb.task_step
            ORDER BY tiktok_coin DESC
                        ''')
        dat.rename(columns={
            "anchor_name":"主播名",
            "illegal_status":"违规信息",
            "tiktok_coin":"当前音浪",
            "diff_hour":"有效直播时长差",
            "diff_days":"有效直播天数差",
            "estimated_rate":"当前比例",
            "estimated_amount":"当前奖励",
            "next_rate":"完成任务预估比例",
            "next_estimated_amount":"完成任务预估奖励"
        }, inplace=True)
        file_path = self.Master_folder + f'push_process_task/进阶活跃任务完成进度_{now_time}.xlsx'
        dat.to_excel(file_path, index=False)
        auto_columns_size(file_path)
        text = '抖音进阶活跃任务监控如下:'
        self.push_file(file_path, robot_url, text)
    def push_tiktok_coin_add(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        dat = self.conn.fetch_dat('''
        SELECT
            anchor_name
            ,aa.anchor_id
            ,near_live_time
            ,gifts_coin
            ,aa.tiktok_coin
            ,aa.tiktok_coin-bb.tiktok_coin up_tiktok_coin
            ,aa.fans_up_number-bb.fans_up_number up_fans
            ,aa.valid_hour
            ,aa.valid_hour - bb.valid_hour up_valid_hour
            ,invalid_hour
            ,aa.video_number
            ,aa.video_number-bb.video_number up_video_number
            ,aa.video_play_number
            ,aa.video_play_number-bb.video_play_number up_video_play_number
            ,aa.video_like_number
            ,aa.video_like_number-bb.video_like_number up_video_like_number
            ,aa.video_remark_number
            ,aa.video_remark_number-bb.video_remark_number up_video_remark_number
        FROM
        (SELECT
            anchor_name
            ,anchor_id
            ,near_live_time
            ,fans_up_number
            ,gifts_coin
            ,tiktok_coin
            ,valid_hour
            ,live_hour-valid_hour invalid_hour
            ,video_number
            ,video_play_number
            ,video_like_number
            ,video_remark_number
        FROM
            tiktok.ods_anchor_dat_lst
        WHERE
            update_date = CURRENT_DATE and tiktok_coin >0
        )aa
        LEFT JOIN
        (SELECT
            anchor_id
            ,fans_up_number
            ,tiktok_coin
            ,valid_hour
            ,video_number
            ,video_play_number
            ,video_like_number
            ,video_remark_number
        FROM
            tiktok.ods_anchor_dat_lst
        WHERE
            update_date = DATE_SUB(CURRENT_DATE,INTERVAL 1 day)
        )bb
        ON aa.anchor_id = bb.anchor_id
                                ''')
        dat.rename(columns={
            "anchor_name": "主播名",
            "anchor_id": "主播ID",
            "near_live_time": "最近开播",
            "gifts_coin": "本月礼物音浪",
            "tiktok_coin": "本月总音浪",
            "up_tiktok_coin": "昨日新增音浪",
            "up_fans": "昨日新增粉丝",
            "valid_hour": "本月有效直播时长",
            "up_valid_hour": "昨日新增有效时长",
            "invalid_hour": "本月无效直播时长",
            "video_number": "本月投稿数",
            "up_video_number": "昨天投稿数",
            "video_play_number": "本月视频播放量",
            "up_video_play_number": "昨日视频播放量",
            "video_like_number": "本月视频点赞量",
            "up_video_like_number": "昨日视频点赞量",
            "video_remark_number": "本月视频评论量",
            "up_video_remark_number": "昨日视频评论量"
        }, inplace=True)
        file_path = self.Master_folder + f'push_anchor_up_data/抖音数据监控_{now_time}.xlsx'
        dat.to_excel(file_path, index=False)
        auto_columns_size(file_path)
        text = '抖音直播&短视频数据监控如下:'
        self.push_file(file_path, robot_url, text)
    def push_tiktok_process_video_task(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        dat = self.conn.fetch_dat('''
        SELECT
            anchor_name
            ,tiktok_id
            ,illegal_status
            ,aa.task_type
            ,video_number
            ,tiktok_coin
            ,estimated_rate
            ,estimated_amount
            ,task_video_number-video_number video_dif
            ,task_rate*tiktok_coin*0.1 task_amount
            ,task_rate*tiktok_coin*0.1 - estimated_amount amount_diff
        FROM
        (SELECT
            *
        FROM
            tiktok.ods_process_video_lst
        WHERE
            update_date =CURRENT_DATE
        )aa
        LEFT JOIN
        (
        SELECT
            *
        FROM
            task.task_tiktok_video
        )bb
        ON	aa.task_type = bb.task_type and aa.now_step +1 = bb.task_step

                                ''')
        dat.rename(columns={
            "anchor_name": "主播名",
            "tiktok_id": "主播ID",
            "illegal_status": "违规信息",
            "task_type": "短视频任务类型",
            "video_number": "完成短视频数",
            "tiktok_coin": "当前音浪",
            "estimated_rate": "奖励比例",
            "estimated_amount": "奖励金额",
            "video_dif": "下阶段视频数差",
            "task_amount": "下阶段预估金额",
            "amount_diff": "预估增加金额"
        }, inplace=True)
        file_path = self.Master_folder + f'push_process_video_task/进阶短视频任务监控_{now_time}.xlsx'
        dat.to_excel(file_path, index=False)
        auto_columns_size(file_path)
        text = '进阶短视频任务监控如下:'
        self.push_file(file_path, robot_url, text)
    def push_log(self, robot_url):
        now_time = datetime.now().strftime('%Y%m%d%H%M')
        update = self.conn.fetch_dat('''
        SELECT
            *
            ,case when end_time is not null then 'success' else 'fail' END status_
        FROM
            log.log_v1
        ORDER BY start_time DESC
            limit 20
    ''')
        file_path = self.Master_folder + f'push_test/数据更新日志推送_{now_time}.xlsx'
        update.to_excel(file_path, sheet_name='update', index=False)
        spider = self.conn.fetch_dat('''
            SELECT
                *
                ,case when end_time is not null then 'success' else 'fail' END status_
            FROM
                log.log_v2
            ORDER BY start_time DESC
            limit 20
        ''')
        append_to_excel(spider, file_path, sheet_name='spider')
        auto_columns_size(file_path)
        self.push_file(file_path, robot_url)